Review Meeting November 21, 2006

Agenda

1. Project Background (5 minutes - 2:05)
2. 4 Use Case Reviews (5 minutes - 2:10)
3. By Use Case Discuss Activity Diagram and Proposed Solution
   1. Create New Device in Instru (10 minutes - 2:20)
   2. Edit Device in Instru (10 minutes - 2:30)
   3. Create New Device in SSDS (15 minutes - 2:45)
   4. Edit Device in SSDS (15 minutes - 3:00)

Attendees:

• Kevin Gomes
• Reiko Michisaki
• Neil Conner
• Francisco Chavez
• Rich Schramm
• John Graybeal
• Aaron Marburg

Meeting Notes:

Project Background

In our initial effort, we will be merging data from SSDS to BOG and back, where both databases have overlapping data.
Eventually we'll try to merge back some of Paul's unique information back into the SSDS database.

Will the information in SSDS be easy to get to? Yes, we'll have pages. (Desire expressed to change those.)

Clarification of databases: "Instru" and "Trans" database in BOG is in fact Paul's database. (The MS Access database no longer exists, it has been moved into BOG although the Access interface is still used.)

This is a two-way system, but with the catch that not all the SSDS instrument entries will go into BOG. Paul will have right of refusal of an instrument into BOG (which is a subset).

Checkpoints will exist so that Paul can approve changes, before they are included. We were trying to be non-intrusive on Paul's current processes and data store. Someday it would be nice to have a single table in one database, but for now we aren't going to try to fit all the instruments into the operational (BOG) database. (SSDS will contain all the devices.)

One could implement a single table with a flag to limit Paul's view? No, because some of Paul's fields do not map gracefully to SSDS tables, e.g., the people fields. Could you put everything from SSDS into BOG, but limit what Paul sees? Yes, but it's a little intrusive, we wanted to have minimal impact on OSG in the first stages.

Have we talked to accounting? They track all fixed assets, we should consider that at the same time (e.g., add a field to manage data of interest to Jim R).

What's the allowed latency? Let's look at this in context of a use case.

Does Paul have write permission to BOG? Just to those two tables.

Where are calibration files tracked? In BOG Transaction table there are some calibration references. SSDS has the Resource field, but we are not using it yet. Keep calibration files in mind as a key capability for the end product. We would use a URL to point to the calibration file(s) when we do implement it.

Note it would be nice to have a physical store for all the calibration files, so they are all in a consistent location.

Put list of variables in each table on the Wiki.

Noted that table in BOG is not complete, not all items have values entered.

Will we be able to access this device information via an interface in SSDS? Yes. Why was it originally in BOG? Just to be in a SQL database.

Would be nice for anyone to have access to BOG database (not currently the case). There are individuals who are interested in access. Probably leave the model with individual authenticated access.

There is everyone/guest access to the SSDS SQL interface, and the web interface is wide open.

4 Use Case Reviews: By Use Case Discussion

Create New Device in Instru

This is the shortest latency case, where a new instrument in BOG should immediately check the contents of SSDS for congruent information.

When Paul creates a new device he'll enter a new transaction. At this time we're not trying to map the transaction data, we'll revisit that later.

Does the BOG application provide all the fields needed to search for a match, so that syntactical differences are normalized? We'll go through the list as a first step to normalize the current entries, working with Paul to maximize consistency. In the device creation page for SSDS it will be constrained, unlike the open field currently used.

What about fields in SSDS like instrument type that BOG doesn't care about but needs (to match keys)? There are only 5 used for search, BOG has them although some normalization between the two is required. We'll agree on rules with Paul and program the synchronization accordingly.

What is the server in SSDS, a SQL server? Can these talk? Yes, they're on the same server.

Edit Device in Instru

What is latency? Probably within an hour is fine. (We don't know of any 'side-by-side' use cases, where two people might be working simultaneously in the two tables.)

Does SSDS provide a last updated field? No, it doesn't. We'll be turning on history tables that would enable access to that functionality. This is a very common question Paul asks.

Create New Device in SSDS

How quickly does a new device in SSDS have to be accessible in BOG? We think not quickly.
Confirm this. (See also previous section for similar question.)

Once an 'accept with edit' happens, another email will get issued once the edit happens? Yes.

AI-> If device is rejected and later edited, it will exist in SSDS but not BOG, so the update action would get confused.
Response: Yes, many actions may get confused if editing happens before OSG acts. Comment added (may require redesign so data does not go into table until BOG response received.

AI-> Note that the "insert new instrument in instru BOG" step will trigger the other use case, so if we use triggers we'll have to trap that case.
Response: "Insert new instrument in Instru BOG" is a trigger of both use cases, so the other use case is not triggered by this action.

We will create an access-controlled capability to create devices (not wide open like it is now).

How will trigger accomplish sending mail? A stored procedure can do it. We don't implement SQLMail. Wouldn't be too hard to implement in current version, but will require investigation, and then would change for 2005 implementation (Neil will be trying to implement around January).

Edit Device in SSDS

AI-> "No" Arrow to final box comes from wrong box, should come from diamond.
Response: Could not determine specifically what this refers to, no similar problem was found. Other improvements may have taken care of it.

AI-> "Is change approved?" should be explicit about who approves change.
Response: Done.

What happens if OSG accidentally rejects a change, instead of accepting it? We can manually fire the stored procedure.

If the SSDSAdmin does not react to the notification of device edit, what happens? This introduces a latency issue, if no action is taken it introduces an unexplained discrepancy.
AI-> Look at this more closely.
Response: This is correct. It has been noted on the diagram.

Will a global update cause many triggers? Umm, yes.

Final Discussion

Francisco wants to answer:

• Where is everything?
• What is the complete history of any given thing?

Batch processing would be extremely valuable addition. (Currently is not working, John says Paul says.)

Each one of these use cases fires companion use case, so mechanism needed to trap those (and the bulk update case).

Do you want this specifically for one SQL server, or do you want it deployable anywhere? (Raises trigger/procedures architectural question.)

Recommendation: Keep as little work going on as possible inside the trigger. If one database is down, trigger will fail and this will be reflected in UI. If you don't need atomic real-time transaction, this will create headaches. Trigger will wait for reply from stored procedures. Dirty bit might be a better answer, with a second table to capture the row/timestamp for a later SQL or Perl job. Making it external this way gives you a lot more control over processing and less likelihood of hosing Paul's user interface. Stored procedures couples DBs too tightly.

Watch error paths on flow charts – how will notification occur on failures? (This is potentialy complex on a multiple-database system.)

If you wanted to go to one table someday, look into creating a view right now that does it. Can you even do an Access-linked table to a view? We think so. We think it is tractable to put things together into one table, but it is more invasive to Paul's practices. Would make the job a lot easier to be working within a single database. Even if we need to change it out later, it will be easy to copy it over when it is needed.

Response: Based on the discussion, we have investigated and are currently pursuing a single-database solution. (We have successfully tested the use of Access through views.)